package meta

var sqlInsertColumn = `
INSERT INTO meta_columns (
    table_id, tenant_id,title, name, type, is_auto_increment, source, method, param, is_prefab,
    is_nullable, default_value, is_pk, is_fk, fk_column_id
) VALUES (
    @table_id, @tenant_id,@title, @name, @type, @is_auto_increment, @source, @method, @param, @is_prefab,
     @is_nullable, @default_value, @is_pk, @is_fk, @fk_column_id
);
`

const existsColumn = `
SELECT 1 FROM meta_columns WHERE tenant_id = @tenant_id AND table_id = @table_id AND name = @name;
`

var sqlUpdateColumn = `
UPDATE meta_columns SET 
    title = @title, name = @name, type = @type, is_auto_increment = @is_auto_increment, source = @source, 
    method = @method, param = @param, is_prefab = @is_prefab, is_nullable = @is_nullable, 
    default_value = @default_value, is_pk = @is_pk, is_fk = @is_fk, fk_column_id = @fk_column_id, is_dict_value = @is_dict_value, 
    is_dict_label = @is_dict_label, is_like_query = @is_like_query, is_dict_group = @is_dict_group,
    updated_at = CURRENT_TIMESTAMP 
WHERE 
    tenant_id = @tenant_id AND column_id = @column_id;
`

var sqlGetTablesAndColumns = `
SELECT 
    t.table_id, 
    t.title AS table_title, 
    t.name AS table_name, 
    t.description as table_description, 
    c.column_id, 
    c.title, 
    c.name, 
    c.type, 
    c.source, 
    c.method, 
    c.param, 
    c.is_prefab, 
    c.is_nullable, 
    c.is_auto_increment, 
    c.default_value, 
    c.is_pk, 
    c.is_fk, 
    c.fk_column_id, 
    c.is_dict_value, 
    c.is_dict_label, 
    c.is_like_query, 
    c.is_dict_group
FROM 
    meta_tables t
LEFT JOIN 
    meta_columns c ON t.table_id = c.table_id
WHERE t.tenant_id=@tenant_id
    &t.table_id
    &t.name
ORDER BY 
   t.table_id desc, c.is_pk desc,  c.is_prefab asc, c.sort_order asc,c.column_id asc;
`

var sqlGetColumnName = `
SELECT 
    c.name
FROM 
    meta_columns c
WHERE 
    c.tenant_id = @tenant_id and c.column_id = @column_id
`

var sqlGetColumnByColumnID = `
SELECT 
    t.table_id, 
    t.title AS table_title, 
    t.name AS table_name, 
    t.description as table_description, 
    c.column_id, 
    c.title, 
    c.name, 
    c.type, 
    c.source, 
    c.method, 
    c.param, 
    c.is_prefab, 
    c.is_nullable, 
    c.is_auto_increment, 
    c.default_value, 
    c.is_pk, 
    c.is_fk, 
    c.fk_column_id, 
    c.is_dict_value, 
    c.is_dict_label, 
    c.is_like_query, 
    c.is_dict_group
FROM 
    meta_tables t
LEFT JOIN 
    meta_columns c ON t.table_id = c.table_id
WHERE 
    t.tenant_id = @tenant_id and column_id = @column_id
`
